package cn.hls.dao.impl;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.Query;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;

import cn.hls.dao.CarDao;
import cn.hls.entity.Car;
import cn.hls.entity.CertSet;
import cn.hls.entity.vo.PageInfo;
import cn.hls.exception.BussinessException;
import cn.hls.until.GenericsUtils;
import cn.hlsconstant.DataAssessEnum;

 
/**
 *@fileName: CarDaoImpl.java
 *@package: cn.christ.dao.impl
 *@Description:  车辆基本信息数据接口实现类
 *@author: yinghui.zhang
 *@email: uuhui@163.com
 *@date: 2015-7-18
 */
@Component("carDao")
public class CarDaoImpl extends BaseDaoImpl<Car> implements CarDao {

	@Override
	public PageInfo<Car> loadCarInfo(Car entity, PageInfo<Car> page) throws Exception {
		// 定义查询Hql语句
		StringBuilder selectHql=new StringBuilder("from Car p inner join fetch p.carCategory c left join  fetch p.company com inner join fetch p.driver d inner join fetch p.escort es ");
		// 定义汇总Hql语句
		StringBuilder counthql = new StringBuilder("select count(p.id) from Car p inner join p.carCategory c  left join  p.company com inner join p.driver d inner join p.escort es ");
		//定义查询条件hql
		StringBuilder conditionHql=new StringBuilder("where p.deleted=? ");
		// 定义查询参数
		List<Object> paramList = new ArrayList<Object>();
		paramList.add(false);
		if (entity != null) {
			//按车牌号
			 if(StringUtils.hasText(entity.getLicenseNum())){
				 conditionHql.append("and licenseNum like ? ");
				 paramList.add("%"+entity.getLicenseNum().trim()+"%");
			 }
			 //按挂车车牌号查询
			 if(StringUtils.hasText(entity.getLicenseNum2())){
				 conditionHql.append("and licenseNum2 like ?  ");
				 paramList.add("%"+entity.getLicenseNum2().trim()+"%");
			 }
			 //按车辆类型信息
			 if(entity.getCarCategory()!=null){
				 if(entity.getCarCategory().getId()!=null){
					 conditionHql.append("and c.id=? ");
					 paramList.add(entity.getCarCategory().getId());
				 }
			 }
			 //按驾驶员查询
			 if(entity.getDriver()!=null){
			 if(StringUtils.hasText(entity.getDriver().getName())){
				 conditionHql.append("and driver like ? ");
				 paramList.add("%"+entity.getDriver().getName().trim()+"%");
			 }
			 }
			 
			 //按押运员查询
			 if(entity.getEscort()!=null){
			 if(StringUtils.hasText(entity.getEscort().getName())){
				 conditionHql.append("and escort like ? ");
				 paramList.add("%"+entity.getEscort().getName().trim()+"%");
			 }
			 }
			 
			 //按企业名称查询
			 if(entity.getCompany()!=null){
				 if(StringUtils.hasText(entity.getCompany().getName())){
					 conditionHql.append("and com.name like ? ");
					 paramList.add("%"+entity.getCompany().getName().trim());
				 }
				 if(entity.getCompany().getId()!=null){
					 conditionHql.append("and com.id=? ");
					 paramList.add(entity.getCompany().getId());
				 }
			 }
			  
			 //载重量
			 if(entity.getCarCategory()!=null){
				 conditionHql.append("and p.carryCapacity=? ");
				 paramList.add(entity.getCarCategory());
			 }
			 if(StringUtils.hasText(entity.getSearchAttribute())){
				 conditionHql.append("and p."+entity.getSearchAttribute()+"<=sysdate and p."+entity.getSearchAttribute()+" is not null");
			 }
			 
			 
		}
		counthql. append(conditionHql);
		selectHql.append(conditionHql);
		selectHql.append(" order by p.createTime");
		return find(selectHql.toString(), counthql.toString(), paramList.toArray(), page);
	}
	

	@Override
	public void delete(Object entityId) throws Exception {
		String []ids=(String[])entityId;
		StringBuilder hql=new StringBuilder("update Car set deleted=? where id in (");
		List<Object>params=new ArrayList<Object>();
		params.add(true);
		for (String id:ids){
			hql.append("?,");
			params.add(Long.valueOf(id));
		}	
		hql.delete(hql.length()-1, hql.length());
		hql.append(")");
		 try {
			executeHQLUpdate(hql.toString(), params.toArray());
		} catch (Exception e) {
			e.printStackTrace();
			throw new BussinessException(DataAssessEnum.UPDATE_ERROR,"更新车辆基本信息失败" );
		}
	}

	 
	 
	@Override
		public boolean exists(Car entity)throws Exception{
			StringBuilder selectHql=new StringBuilder("select count(id) from Car where deleted=? ");
			List<Object>params=new ArrayList<Object>();
			params.add(false);
			if(entity!=null){
				if(StringUtils.hasText(entity.getLicenseNum())){//姓名
					selectHql.append("and licenseNum=? ");
					params.add(entity.getLicenseNum().trim());
				}
			 
				if(StringUtils.hasText(entity.getLicenseNum2())){//身份证号
					selectHql.append("and licenseNum2=? ");
					params.add(entity.getLicenseNum().trim());
				}
			
			}
			Query query=this.getSession().createQuery(selectHql.toString());
			for(int i=0;i<params.size();i++){
				query.setParameter(i, params.get(i));
			}
			Long c = Long.valueOf(((Number) query.uniqueResult()).longValue());
			return c.intValue()>0?true:false;
	}

	@Override
	public List<Car> loadCarList(Car entity) throws Exception {
		StringBuilder hql=new StringBuilder("from Car p inner join fetch p.carCategory c left join  fetch p.company com inner join fetch p.driver d inner join fetch p.escort es where p.deleted=? ");
		List<Object>paramList=new ArrayList<Object>();
		paramList.add(false);
		if (entity != null) {
			//按车牌号
			 if(StringUtils.hasText(entity.getLicenseNum())){
				 hql.append("and licenseNum like ? ");
				 paramList.add("%"+entity.getLicenseNum().trim()+"%");
			 }
			 //按挂车车牌号查询
			 if(StringUtils.hasText(entity.getLicenseNum2())){
				 hql.append("and licenseNum2 like ?  ");
				 paramList.add("%"+entity.getLicenseNum2().trim()+"%");
			 }
			 //按车辆类型信息
			 if(entity.getCarCategory()!=null){
				 if(entity.getCarCategory().getId()!=null){
					 hql.append("and c.id=? ");
					 paramList.add(entity.getCarCategory().getId());
				 }
			 }
			 //按驾驶员查询
			 if(entity.getDriver()!=null){
				 if(StringUtils.hasText(entity.getDriver().getName())){
					 hql.append("and driver like ? ");
					 paramList.add("%"+entity.getDriver().getName().trim()+"%");
				 }
			 }
			 
			 //按押运员查询
			 if(entity.getEscort()!=null){
				 if(StringUtils.hasText(entity.getEscort().getName())){
					 hql.append("and escort like ? ");
					 paramList.add("%"+entity.getEscort().getName().trim()+"%");
				 }
			 }
			 
			 //按企业名称查询
			 if(entity.getCompany()!=null){
				 if(StringUtils.hasText(entity.getCompany().getName())){
					 hql.append("and com.name like ? ");
					 paramList.add("%"+entity.getCompany().getName().trim());
				 }
				 if(entity.getCompany().getId()!=null){
					 hql.append("and com.id=? ");
					 paramList.add(entity.getCompany().getId());
				 }
			 }
			  
			 //载重量
			 if(entity.getCarCategory()!=null){
				 hql.append("and p.carryCapacity=? ");
				 paramList.add(entity.getCarCategory());
			 }
			 if(StringUtils.hasText(entity.getSearchAttribute())){
				 hql.append("and p."+entity.getSearchAttribute()+"<=? and p."+entity.getSearchAttribute()+"is not null");
				 paramList.add("sysdate");
			 }
			 
		}
		return findByHql(hql.toString(), paramList.toArray());
	}


	@Override
	public Map<String, Integer> loadExpiredCar(Long companyId,Map<String, CertSet> certSetMap)throws Exception {
		Map<String,Integer>map=new HashMap<String, Integer>();
		String hql=null;
		if(companyId==null){
			//强制保险过期汇总统计
			hql="select count(p.id) from Car p where p.deleted=? and forcedInsuranceTime is not null and forcedInsuranceTime<=? ";
			map.put("forcedInsurance", countByHql(hql,new Object[]{false,GenericsUtils.getPreMonthTime(new Date(),certSetMap.get("forcedInsurance")==null?1:certSetMap.get("forcedInsurance").getPreMonth().intValue())}).intValue());
			//第三责任险过期汇总统计
			hql="select count(p.id) from Car p where p.deleted=? and thirdInsuranceTime is not null and thirdInsuranceTime<=? ";
			map.put("thirdInsurance", countByHql(hql, new Object[]{false,GenericsUtils.getPreMonthTime(new Date(),certSetMap.get("thirdInsurance")==null?1:certSetMap.get("thirdInsurance").getPreMonth().intValue())}).intValue());
			//承运人责任险过期汇总统计
			hql="select count(p.id) from Car p where p.deleted=? and carrierInsuranceTime is not null and carrierInsuranceTime<=? ";
			map.put("carrierInsurance", countByHql(hql, new Object[]{false,GenericsUtils.getPreMonthTime(new Date(),certSetMap.get("carrierInsurance")==null?1:certSetMap.get("carrierInsurance").getPreMonth().intValue())}).intValue());
			//行驶证年审过期汇总统计
			hql="select count(p.id) from Car p where p.deleted=? and driveExamTime is not null and driveExamTime<=? ";
			map.put("driveExam", countByHql(hql, new Object[]{false,GenericsUtils.getPreMonthTime(new Date(),certSetMap.get("driveExam")==null?1:certSetMap.get("driveExam").getPreMonth().intValue())}).intValue());
			//道路运输证年审过期汇总统计
			hql="select count(p.id) from Car p where p.deleted=? and transLicenseExamTime is not null and transLicenseExamTime<=? ";
			map.put("transLicenseExam", countByHql(hql, new Object[]{false,GenericsUtils.getPreMonthTime(new Date(),certSetMap.get("transLicenseExam")==null?1:certSetMap.get("transLicenseExam").getPreMonth().intValue())}).intValue());
			//罐体年检过期汇总统计
			hql="select count(p.id) from Car p where p.deleted=? and tankExamTime is not null and tankExamTime<=? ";
			map.put("tankExam", countByHql(hql, new Object[]{false,GenericsUtils.getPreMonthTime(new Date(),certSetMap.get("tankExam")==null?1:certSetMap.get("tankExam").getPreMonth().intValue())}).intValue());
			//技术等级评定过期汇总统计
			hql="select count(p.id) from Car p where p.deleted=? and techicalGradeTime is not null and techicalGradeTime<=? ";
			map.put("techicalGrade", countByHql(hql, new Object[]{false,GenericsUtils.getPreMonthTime(new Date(),certSetMap.get("techicalGrade")==null?1:certSetMap.get("techicalGrade").getPreMonth().intValue())}).intValue());
			//二级维护过期汇总统计
			hql="select count(p.id) from Car p where p.deleted=? and secondMaintenanceTime is not null and secondMaintenanceTime<=? ";
			map.put("secondMaintenance", countByHql(hql, new Object[]{false,GenericsUtils.getPreMonthTime(new Date(),certSetMap.get("secondMaintenance")==null?1:certSetMap.get("secondMaintenance").getPreMonth().intValue())}).intValue());
		}else{
			//强制保险过期汇总统计
			hql="select count(p.id) from Car p inner join p.company c where p.deleted=? and c.id=? and p.forcedInsuranceTime is not null and p.forcedInsuranceTime<=? ";
			map.put("forcedInsurance", countByHql(hql,new Object[]{false,companyId,GenericsUtils.getPreMonthTime(new Date(),certSetMap.get("forcedInsurance")==null?1:certSetMap.get("forcedInsurance").getPreMonth().intValue())}).intValue());
			//第三责任险过期汇总统计
			hql="select count(p.id) from Car p inner join p.company c where p.deleted=? and c.id=? and p.thirdInsuranceTime is not null and p.thirdInsuranceTime<=? ";
			map.put("thirdInsurance", countByHql(hql, new Object[]{false,companyId,GenericsUtils.getPreMonthTime(new Date(),certSetMap.get("thirdInsurance")==null?1:certSetMap.get("thirdInsurance").getPreMonth().intValue())}).intValue());
			//承运人责任险过期汇总统计
			hql="select count(p.id) from Car p inner join p.company c where p.deleted=? and c.id=? and p.carrierInsuranceTime is not null and p.carrierInsuranceTime<=? ";
			map.put("carrierInsurance", countByHql(hql, new Object[]{false,companyId,GenericsUtils.getPreMonthTime(new Date(),certSetMap.get("carrierInsurance")==null?1:certSetMap.get("carrierInsurance").getPreMonth().intValue())}).intValue());
			//行驶证年审过期汇总统计
			hql="select count(p.id) from Car p inner join p.company c where p.deleted=? and c.id=? and p.driveExamTime is not null and p.driveExamTime<=? ";
			map.put("driveExam", countByHql(hql, new Object[]{false,companyId,GenericsUtils.getPreMonthTime(new Date(),certSetMap.get("driveExam")==null?1:certSetMap.get("driveExam").getPreMonth().intValue())}).intValue());
			//道路运输证年审过期汇总统计
			hql="select count(p.id) from Car p inner join p.company c where p.deleted=? and c.id=? and p.transLicenseExamTime is not null and p.transLicenseExamTime<=? ";
			map.put("transLicenseExam", countByHql(hql, new Object[]{false,companyId,GenericsUtils.getPreMonthTime(new Date(),certSetMap.get("transLicenseExam")==null?1:certSetMap.get("transLicenseExam").getPreMonth().intValue())}).intValue());
			//罐体年检过期汇总统计
			hql="select count(p.id) from Car p inner join p.company c where p.deleted=? and c.id=? and p.tankExamTime is not null and p.tankExamTime<=? ";
			map.put("tankExam", countByHql(hql, new Object[]{false,companyId,GenericsUtils.getPreMonthTime(new Date(),certSetMap.get("tankExam")==null?1:certSetMap.get("tankExam").getPreMonth().intValue())}).intValue());
			//技术等级评定过期汇总统计
			hql="select count(p.id) from Car p inner join p.company c where p.deleted=? and c.id=? and p.techicalGradeTime is not null and p.techicalGradeTime<=? ";
			map.put("techicalGrade", countByHql(hql, new Object[]{false,companyId,GenericsUtils.getPreMonthTime(new Date(),certSetMap.get("techicalGrade")==null?1:certSetMap.get("techicalGrade").getPreMonth().intValue())}).intValue());
			//二级维护过期汇总统计
			hql="select count(p.id) from Car p inner join p.company c where p.deleted=? and c.id=? and p.secondMaintenanceTime is not null and p.secondMaintenanceTime<=? ";
			map.put("secondMaintenance", countByHql(hql, new Object[]{false,companyId,GenericsUtils.getPreMonthTime(new Date(),certSetMap.get("secondMaintenance")==null?1:certSetMap.get("secondMaintenance").getPreMonth().intValue())}).intValue());

		}
		return map;	
	}
	
	@Override
	public List<Car>loadExpiredCarList(Car entity,Map<String, CertSet> certSetMap)throws Exception{
		StringBuilder hql=new  StringBuilder("from Car p inner join fetch p.carCategory c ");
		hql.append("left join  fetch p.company com inner join fetch p.driver d inner join fetch p.escort es  where p.deleted=? and (" );
		List<Object>params=new ArrayList<Object>();//定义查询条件
		params.add(false);
		hql.append("(p.forcedInsuranceTime is not null and p.forcedInsuranceTime<=?) or ");////强制保险过期
		params.add(GenericsUtils.getPreMonthTime(new Date(),certSetMap.get("forcedInsurance")==null?1:certSetMap.get("forcedInsurance").getPreMonth().intValue()));
		hql.append("(p.thirdInsuranceTime is not null and p.thirdInsuranceTime<=?)or ");//第三责任险过期
		params.add(GenericsUtils.getPreMonthTime(new Date(),certSetMap.get("thirdInsurance")==null?1:certSetMap.get("thirdInsurance").getPreMonth().intValue()));
		hql.append("(p.carrierInsuranceTime is not null and p.carrierInsuranceTime<=?) or ");//承运人责任险过期
		params.add(GenericsUtils.getPreMonthTime(new Date(),certSetMap.get("carrierInsurance")==null?1:certSetMap.get("carrierInsurance").getPreMonth().intValue()));
		hql.append("(p.driveExamTime is not null and p.driveExamTime<=?) or ");//行驶证年审过期
		params.add(GenericsUtils.getPreMonthTime(new Date(),certSetMap.get("driveExam")==null?1:certSetMap.get("driveExam").getPreMonth().intValue()));
		hql.append("(p.transLicenseExamTime is not null and p.transLicenseExamTime<=?) or ");//道路运输证年审过期"
		params.add(GenericsUtils.getPreMonthTime(new Date(),certSetMap.get("transLicenseExam")==null?1:certSetMap.get("transLicenseExam").getPreMonth().intValue()));
		hql.append("(p.tankExamTime is not null and p.tankExamTime<=?) or ");//罐体年检过期
		params.add(GenericsUtils.getPreMonthTime(new Date(),certSetMap.get("tankExam")==null?1:certSetMap.get("tankExam").getPreMonth().intValue()));
		hql.append("(p.techicalGradeTime is not null and p.techicalGradeTime<=?) or ");//技术等级评定过期
		params.add(GenericsUtils.getPreMonthTime(new Date(),certSetMap.get("techicalGrade")==null?1:certSetMap.get("techicalGrade").getPreMonth().intValue()));
		hql.append("(p.secondMaintenanceTime is not null and p.secondMaintenanceTime<=?)");//二级维护过期
		params.add(GenericsUtils.getPreMonthTime(new Date(),certSetMap.get("secondMaintenance")==null?1:certSetMap.get("secondMaintenance").getPreMonth().intValue()));
		hql.append(")");
		return findByHql(hql.toString(), params.toArray());
	}
	
}
